﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web.Services;

/**
* \brief select, insert, update, delete database sql server
*/
public class DBClass : BaseServ
{
    private int result;
	public DBClass()
	{
        this.result = 0;
    }

    #region sqlHelp
    /**
     * execute procedure insert, update, delete
     * @see ExecuteScalar()
     * @return object
     */
    public virtual object ExecuteScalar(string ProcedureName, params object[] Parameters)
    {
        return SqlHelper.ExecuteScalar(dbConnString, ProcedureName, Parameters);
    }
    /**
     * execute procedure insert, update, delete
     * @see ExecuteNonQuery()
     * @return int
     */
    public virtual int ExecuteNonQuery(string ProcedureName, params object[] Parameters)
    {
        return SqlHelper.ExecuteNonQuery(dbConnString, ProcedureName, Parameters);
    }
    /**
     * get data by procedure
     * @see GetDataRow()
     * @return DataRow
     */
    public virtual DataRow GetDataRow(string ProcedureName, params object[] Parameters)
    {
        return GetDataRow(0, ProcedureName, Parameters);
    }
    /**
     * get datarow in datatable by rowindex
     * @see GetDataRow()
     * @return DataRow
     */
    public virtual DataRow GetDataRow(int RowIndex, string ProcedureName, params object[] Parameters)
    {
        DataTable dt = GetDataTable(0, ProcedureName, Parameters);
        DataRow dr = null;

        if (dt != null)
        {
            if (RowIndex >= 0 && RowIndex < dt.Rows.Count)
            {
                dr = dt.Rows[RowIndex];
            }
            dt.Dispose();
        }
        return dr;
    }
    /**
     * get datatable in dataset by tableindex
     * @see GetDataTable()
     * @return DataTable
     */
    public static DataTable GetDataTable(int TableIndex, string ProcedureName, params object[] Parameters)
    {
        DataSet ds = GetDataSet(ProcedureName, Parameters);
        DataTable dt = null;
        if (ds != null && ds.Tables.Count > 0)
        {
            if (TableIndex >= 0 && TableIndex < ds.Tables.Count)
                dt = ds.Tables[TableIndex];

            ds.Dispose();
        }
        return dt;
    }
    /**
     * get datatable by procedure
     * @see GetDataTable()
     * @return DataTable
     */
    public static DataTable GetDataTable(string ProcedureName, params object[] Parameters)
    {
        DataSet ds = GetDataSet(ProcedureName, Parameters);
        DataTable dt = null;
        if (ds != null && ds.Tables.Count > 0)
        {
            dt = ds.Tables[0];
            ds.Dispose();
        }
        return dt;
    }
    /**
     * get dataset by procedure
     * @see GetDataSet()
     * @return DataSet
     */
    public static DataSet GetDataSet(string ProcedureName, params object[] Parameters)
    {
       return SqlHelper.ExecuteDataset(dbConnString, ProcedureName, Parameters);
    }
    public object GetValueFromDB(string sql)
    {
        return SqlHelper.ExecuteScalar(dbConnString, CommandType.Text, sql);
    }

    #endregion

    #region userinfo
    /**
     * change password user
     * @see ChangePassword()
     * @param username need change password
     * @param pascurrent password current
     * @param passnew password new need change
     * @return value 1 success, 0 failure
     */
    [WebMethod]
    public int ChangePassword(string username, string passcurrent, string passnew, string passhint)
    {
        object result = ExecuteScalar("spChangePassword", username, passcurrent, passnew, passhint);
        return ToSQL.SQLToInt(result);
    }
    /**
     * delete user authorize by id
     * @see Delete_UsersAuthorizes_ById()
     * @param autoid id user authorize need delete
     * @return value 1 success, 0 failure
     */
    [WebMethod]
    public int Delete_UsersAuthorizes_ById(int autoid)
    {
        result = ExecuteNonQuery("spDelete_UserAuthorizes_ById", autoid);
        return result;
    }
    /**
     * insert, update user
     * @see InsertUpdate_Users()
     * @param username if username exist then update, else insert 
     * @return value 1 success, 0 failure
     */
    [WebMethod]
    public int InsertUpdate_Users(string username, string userpass, string passhint, int usertypeid,bool isactive)
    {
        result = ExecuteNonQuery("spInsertUpdate_Users", username, userpass, passhint, usertypeid, isactive);
        return result;
    }
    /**
     * insert, update user contact
     * @see InsertUpdate_UsersContact()
     * @param username if username exist then update, else insert 
     * @return value 1 success, 0 failure
     */
    [WebMethod]
    public int InsertUpdate_UsersContact(string username, string name, string email, string phone, string address,bool IsMain)
    {
        result = ExecuteNonQuery("spInsertUpdate_UserContact", username, name, email, phone, address, IsMain);
        return result;
    }
    /**
     * insert, update user info
     * @see InsertUpdate_UsersInfo()
     * @param username if username exist then update, else insert 
     * @return value 1 success, 0 failure
     */
    [WebMethod]
    public int InsertUpdate_UsersInfo(string username, string firstname, string lastname, DateTime? dateofbirth, bool isgender,
                                string emailaddress, string streetaddress, string citycode, string statecode, string zipcode,
                                string county, string countrycode, string homephone, string mobilephone, string registrationcode)
    {
        result = ExecuteNonQuery("spInsertUpdate_UserInfo", username, firstname, lastname, dateofbirth, isgender,
                                 emailaddress, streetaddress, citycode, statecode, zipcode, county, countrycode, homephone, mobilephone, registrationcode);
        return result;
    }

    /**
     * get user info by username
     * @see GetInfo_UsersAll_ByUserName()
     * @return datarow info user
     */
    public DataRow GetInfo_UsersAll_ByUserName(string username)
    {
        return GetDataRow("spGetInfo_UserAll_ByUserName", username);
    }
    /**
     * get list member type
     * @see GetList_MemberType()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_MemberType()
    {
        return GetDataTable("spGetList_MemberType");
    }
    /**
     * get info rule by username
     * @see GetInfo_Rule_ByUserName()
     * @return datarow
     */
    public DataRow GetInfo_Rule_ByUserName(string username)
    {
          return GetDataRow("spGetInfo_Rule_ByUserName", username);
    }
    /**
     * get list billing type
     * @see GetList_BillingAccountTypes()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_BillingAccountTypes()
    {
        return GetDataTable("spGetList_BillingAccountTypes");
    }
    /**
     * get info user contact by username
     * @see GetInfo_UsersContact_ByUserName()
     * @return datarow
     */
    public DataRow GetInfo_UsersContact_ByUserName(string username)
    {
        return GetDataRow("spGetInfo_UserContact_ByUserName", username);
    }
    /**
     * get list user authorize
     * @see GetList_AuthorizedUser()
     * @return dataset
     */
    [WebMethod]
    public DataSet GetList_AuthorizedUser(string username)
    {
        return GetDataSet("spGetList_AuthorizedUser", username);
    }
    /**
     * get list couontry
     * @see GetList_Countries()
     * @return dataset
     */
    [WebMethod]
    public DataSet GetList_Countries()
    {
        return GetDataSet("spGetList_Countries");
    }
    /**
     * get list states
     * @see GetList_CountryStates()
     * @return dataset
     */
    [WebMethod]
    public DataSet GetList_CountryStates()
    {
        return GetDataSet("spGetList_CountryStates");
    }
    /**
     * get list card type
     * @see GetList_CreditCardType()
     * @return dataset
     */
    public DataSet GetList_CreditCardType()
    {
        return GetDataSet("spGetList_CreditCardType");
    }
    /**
     * get list states by country code
     * @see GetList_CountryStates_ByCountryCode()
     * @return dataset
     */
    [WebMethod]
    public DataSet GetList_CountryStates_ByCountryCode(string countrycode)
    {
        return GetDataSet("spGetList_CountryStates_ByCountryCode", countrycode);
    }
    /**
     * insert user authorize
     * @see Insert_UsersAuthorizes()
     * @return datarow
     */
    public DataRow Insert_UsersAuthorizes(string username, string authorizeduser)
    {
        return GetDataRow("spInsert_UserAuthorizes", username, authorizeduser);//always 1 row
    }
    /**
     * login website
     * @see Login_Website()
     * @return datarow info user login
     */
    public DataRow Login_Website(string username, string userpass, string loginfromcomputerip)
    {
        return GetDataRow("spLogin_Website", username, userpass, loginfromcomputerip);
    }
    /**
     * register new user
     * @see Register_Account_Users()
     * @return datarow user info register
     */
    public DataRow Register_Account_Users(string username, string userpass, string passhint,string firstname, string lastname, 
                                DateTime? dateofbirth, bool isgender, string emailaddress, string streetaddress, string citycode,
                                string statecode, string zipcode, string county, string countrycode, string homephone, string mobilephone, string registeredip, string registrationcode)
    {
        return GetDataRow("spRegister_Account_User", username, userpass, passhint,firstname, lastname, dateofbirth, isgender,
                                 emailaddress, streetaddress, citycode, statecode, zipcode, county, countrycode, homephone, mobilephone, registeredip, registrationcode);//always 1 row
    }
    /**
     * check username register
     * @see Register_Validation_UserName()
     * @return object value 1 username validation, 0 unvalidation
     */
    [WebMethod]
    public object Register_Validation_UserName(string username)
    {
        return ExecuteScalar("spRegister_Validation_UserName", username);// 1 = ok 
    }
    /**
     * check email is exist
     * @see IsExists_Email()
     * @return object value 1 exist, 0 not exist
     */
    public object IsExists_Email(string email)
    {
        return ExecuteScalar("spIsExists_Email", email);// 1 = exist || 0 = no 
    }
    /**
     * check info user forgot password
     * @see Submit_Forgot_Password()
     * @return datarow
     */
    public DataRow Submit_Forgot_Password(string username, DateTime? dateofbirth)
    {
        return GetDataRow("spSubmit_Forgot_Password", username, dateofbirth);//always 1 row
    }
    /**
     * check info user forgot username
     * @see Submit_Forgot_UserName()
     * @return datarow
     */
    public DataRow Submit_Forgot_UserName(string firstname, string lastname, DateTime? dateofbirth, string zipcode)
    {
        return GetDataRow("spSubmit_Forgot_UserName", firstname, lastname, dateofbirth, zipcode);//always 1 row
    }
    /**
     * get list parameter
     * @see GetList_Paramaters()
     * @return dataset
     */
    [WebMethod]
    public DataSet GetList_Paramaters(int Id,string ParaCode,string Description)
    {
        return GetDataSet("spGetList_Paramaters",Id,ParaCode,Description);
    }
    /**
     * get list message, admin message
     * @see GetList_AdminMessages()
     * @return dataset
     */
    [WebMethod]
    public DataSet GetList_AdminMessages(int Id,string Code, string Title)
    {
        return GetDataSet("spGetList_AdminMessages",Id, Code, Title);
    }
    /**
     * insert message, admin message
     * @see InsertUpdate_AdminMessages()
     * @return int value 1 success, 0 failure
     */
    [WebMethod]
    public int InsertUpdate_AdminMessages(int Id, string Code, string Title,string TitleVN,bool Approved,string Note)
    {
        return ToSQL.SQLToInt(ExecuteNonQuery("spInsertUpdate_AdminMessages", Id, Code, Title, TitleVN, Approved, Note));
    }
    /**
     * get list all user
     * @see GetList_Users_Admin()
     * @return dataset
     */
    [WebMethod]
    public DataSet GetList_Users_Admin(string UserName,string LastName,string FirstName,DateTime? FromDateOfBirth,DateTime? ToDateOfBirth)
    {
        return GetDataSet("spGetList_User_Admin",UserName,LastName,FirstName,FromDateOfBirth,ToDateOfBirth);
    }
    /**
     * get list user
     * @see GetList_Users()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_Users(int userid, string username, bool? isactive, string email, DateTime? createdatefrom, DateTime? createdateto)
    {
        return GetDataTable("spGetList_Users", userid, username, isactive, email, createdatefrom, createdateto);
    }
    /**
     * get list code register
     * @see GetList_RegisterCodes()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_RegisterCodes(string Rule,bool? IsUsing,bool? Disable)
    {
        return GetDataTable("spGetList_RegisterCodes",Rule,IsUsing,Disable);
    }
    /**
     * get list user login website
     * @see GetList_User_Login()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_User_Login(string Username, DateTime? LoginDateFrom, DateTime? LogindDateTo)
    {
        return GetDataTable("spGetList_User_Login",Username, LoginDateFrom, LogindDateTo);
    }
    /**
     * active 1 array id
     * @see IsActive_User_ByIds()
     * @parame ids array id
     * @param isactive true active, false deavtive
     * @return int value 1 success, 0 failure
     */
    [WebMethod]
    public int IsActive_User_ByIds(string Ids, bool isActive)
    {
        return ExecuteNonQuery("spIsActive_User_ByIds", Ids, isActive);
    }
    /**
     * insert update parameter
     * @see InsertUpdate_Paramaters()
     * @return int value 1 success, 0 failure
     */
    [WebMethod]
    public int InsertUpdate_Paramaters(int Id, string ParamCode, string Description, string Value, bool Active,string Note)
    {
        return ToSQL.SQLToInt(ExecuteNonQuery("spInsertUpdate_Paramaters",Id, ParamCode, Description, Value, Active,Note));
    }
    /**
     * delete parameter by id
     * @see DeleteParamaters()
     * @return int value 1 success, 0 failure
     */
    [WebMethod]
    public int DeleteParamaters(int AutoId)
    {
        return Convert.ToInt32(ExecuteNonQuery("spDeleteParamaters", AutoId));
    }
    /**
     * delete parameter by id
     * @see DeleteParamaters()
     * @return int value 1 success, 0 failure
     */
    [WebMethod]
    public int Set_RegisterCode_Using(string Code)
    {
        return Convert.ToInt32(ExecuteNonQuery("spSet_RegisterCode_Using", Code));
    }
    /**
     * sent mail from user to user
     * @see Insert_MessageCenter()
     * @return datarow
     */
    public DataRow Insert_MessageCenter(string MailFrom, string Subject, string MailTo, string MessageCenter)
    {
        return GetDataRow("spInsert_MessageCenter", MailFrom, MailTo, Subject, MessageCenter);
    }
    /**
     * delete message by array id
     * @see Delete_MessageCenter_ByIds()
     * @return datarow
     */
    public DataRow Delete_MessageCenter_ByIds(string MessageIds)
    {
        return GetDataRow("spDelete_MessageCenter_ByIds", MessageIds);
    }
    /**
     * delete message trash by array trash id
     * @see Delete_MessageCenter_Trash_ByIds()
     * @return datarow
     */
    public DataRow Delete_MessageCenter_Trash_ByIds(string MessageIds)
    {
        return GetDataRow("spDelete_MessageCenter_Trash_ByIds", MessageIds);
    }
    /**
     * get info pay bill card
     * @see GetInfo_PayBill_CreditCard()
     * @return datarow
     */
    public DataRow GetInfo_PayBill_CreditCard(string username)
    {
        return GetDataRow("spGetInfo_PayBill_CreditCard", username);//always 1 row
    }
    /**
     * get info billing account
     * @see GetInfo_PayBill_CreditCard()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetInfo_BillingAccounts(string username)
    {
        return GetDataTable("spGetInfo_BillingAccounts", username);//always 1 row
    }
    /**
     * get info billing account user
     * @see GetInfo_BillingAccounts_User()
     * @return datarow
     */
    public DataRow GetInfo_BillingAccounts_User(string username)
    {
        return GetDataRow("spGetInfo_BillingAccounts", username);//always 1 row
    }
    /**
     * get list billing account details
     * @see GetList_BillingAccountDetails()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_BillingAccountDetails(string AccountNumber)
    {
        return GetDataTable("spGetList_BillingAccountDetails", AccountNumber);
    }
    /**
     * get list billing payment
     * @see GetList_BillingPayments()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_BillingPayments(string AccountNumber)
    {
        return GetDataTable("spGetList_BillingPayments", AccountNumber);
    }
    /**
     * get list message inbox
     * @see GetList_MessageCenter_Inbox()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_MessageCenter_Inbox(string UserName)
    {
        return GetDataTable("spGetList_MessageCenter_Inbox", UserName);
    }
    /**
     * get list message alarm
     * @see GetList_MessageCenter_Alarm()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_MessageCenter_Alarm(string UserName)
    {
        return GetDataTable("spGetList_MessageCenter_Alarm", UserName);
    }
    /**
     * get list message sent
     * @see GetList_MessageCenter_Sent()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_MessageCenter_Sent(string UserName)
    {
        return GetDataTable("spGetList_MessageCenter_Sent", UserName);
    }
    /**
     * get list message trash
     * @see GetList_MessageCenter_Trash()
     * @return datatable
     */
    [WebMethod]
    public DataTable GetList_MessageCenter_Trash(string UserName)
    {
        return GetDataTable("spGetList_MessageCenter_Trash", UserName);
    }
    /**
     * get info message by id
     * @see GetInfo_MessageCenter_ById()
     * @return datarow
     */
    public DataRow GetInfo_MessageCenter_ById(int MessageId)
    {
        return GetDataRow("spGetInfo_MessageCenter_ById", MessageId);
    }
    /**
     * get email by username
     * @see GetEmail_ByUserName()
     * @return object value is email
     */
    public object GetEmail_ByUserName(string UserName)
    {
        return ExecuteScalar("spGetEmail_ByUserName", UserName);
    }
    /**
     * update global setting
     * @see UpdateGlobalSetting()
     * @return int value 1 success, 0 failure
     */
    [WebMethod]
    public int UpdateGlobalSetting(string UserName)
    {
        return ExecuteNonQuery("spUpdateGlobal", UserName);
    }
    /**
     * get list user temperature
     * @see GetList_UserTemperature()
     * @return datatable
     */
    #endregion

    #region user
    public DataRow web_sp_LoginWebsite(string username, string pword)
    {
        return GetDataRow("web_sp_LoginWebsite", username, pword);
    }

    #endregion

    #region Soft
    public DataTable GetList_Employee(string UserCode,string EmpCode,string FirstName,string LastName,string Deptcode,string Sectioncode,DateTime? FromJoindate,DateTime ?ToJoinDate,DateTime? FromBirthDay,DateTime? ToBirthDay)
    {
        return GetDataTable("web_sp_GetList_Employee", UserCode,EmpCode,FirstName,LastName,Deptcode,Sectioncode,FromJoindate,ToJoinDate,FromBirthDay,ToBirthDay);
    }
    public DataTable Select_EmpDaily(string UserCode,DateTime? FromDate,DateTime? ToDate,string DeptCodeL,string SectionCodeL,string EmpCodeL,string ShiftCodeL,bool? IsLock,bool? IsIn,bool? IsOut,bool? IsLeaveDay,bool? IsLateIn,bool? IsEarlyOut)
    {
        return GetDataTable("web_sp_Select_EmpDaily", UserCode,FromDate,ToDate,DeptCodeL,SectionCodeL,EmpCodeL,ShiftCodeL,IsLock,IsIn,IsOut,IsLeaveDay,IsLateIn,IsEarlyOut);
    }
    public DataTable GetList_Department()
    {
        return GetDataTable("web_GetList_Department");
    }
    public DataTable GetList_Section(string Department)
    {
        return GetDataTable("web_GetList_Section",Department);
    }
    #endregion
}